﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Drawing.Imaging;

namespace FaceFromCamera
{
    class DatabaseModel
    {
        public SQLiteConnection connect { get; set; }
        #region SingLeTon
        static DatabaseModel instance;
        public static DatabaseModel Instance {
            get {
                if (instance == null)
                    instance = new DatabaseModel();
                return instance;
            }
        }
        #endregion


        public DatabaseModel()
        {
            String conString = "Data Source=FDatabase.s3db";
            connect = new SQLiteConnection(conString);
            connect.Open();

        }

        // 1. Funkcja zwracająca obiekt Person na podstawie ID
        public Person GetUser(int ID)
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * ");
            query.Append("FROM Users WHERE ID = '"+ ID +"'");

            using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), connect))
            {
                using (SQLiteDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        return new Person(dr.GetValue(0), dr.GetValue(1), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4));
                    }
                }
            }
            
            return new Person();
        }

        // 2. Pobieranie wszystkich zdjęć
        public AllPhoto DisplayImage()
        {
            object theImg;

            string sql = "SELECT ID_IMAGE, IMAGE FROM Images";
            SQLiteCommand cmd = new SQLiteCommand(sql, connect);
            cmd.CommandType = CommandType.Text;

            AllPhoto photos = new AllPhoto();

            using (SQLiteDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    photos.id.Add(Convert.ToInt32(dr.GetValue(0)));
                    theImg = dr.GetValue(1);
                    photos.photo.Add(Image.FromStream(new MemoryStream((byte[])theImg)));
                }
            }
            return photos;

        }
        // 3. Pobbieranie zdjęć na podstawie ID
        public List<Image> GetImage(int id)
        {
      
            SQLiteCommand cmd = new SQLiteCommand(connect);
            SQLiteParameter param1 = new SQLiteParameter("@param1");
            cmd.CommandText = "SELECT IMAGE FROM Images WHERE ID_IMAGE=@param1";

            cmd.Parameters.Add(param1);

            param1.Value = id;

            List<Image> photos = new List<Image>();
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    photos.Add(Image.FromStream(new MemoryStream((byte[])reader.GetValue(0))));
                }
            }

            return photos;

        }
        // 4. Zapisywanie danych  klasy Person do bazy
        public void RemovePerson(Person P)
        {
            SQLiteCommand mycommand = new SQLiteCommand(connect);
            SQLiteParameter param1 = new SQLiteParameter("@param1");
            SQLiteParameter param2 = new SQLiteParameter("@param2");
            SQLiteParameter param3 = new SQLiteParameter("@param3");
            SQLiteParameter param4 = new SQLiteParameter("@param4");
            mycommand.CommandText = "UPDATE Users SET LOGIN=@param1, PASSWORD=@param2, NAME=@param3, SURNAME=@param4 WHERE ID =" + P.id.ToString();
            mycommand.Parameters.Add(param1);
            mycommand.Parameters.Add(param2);
            mycommand.Parameters.Add(param3);
            mycommand.Parameters.Add(param4);

            param1.Value = P.login;
            param2.Value = P.password;
            param3.Value = P.name;
            param4.Value = P.surname;
            mycommand.ExecuteNonQuery();

            mycommand.CommandText = "DELETE FROM Images WHERE ID_IMAGE = " + P.id.ToString();
            mycommand.ExecuteNonQuery();




        }
        public void AddPersonToDatabase(Person person)
        {
            string query1 = "INSERT INTO Users(ID,LOGIN,PASSWORD,NAME,SURNAME) VALUES (null,@param1, @param2, @param3,  @param4)";

            SQLiteCommand sql_cmd = new SQLiteCommand(connect);

            SQLiteParameter param1 = new SQLiteParameter("@param1");
            SQLiteParameter param2 = new SQLiteParameter("@param2");
            SQLiteParameter param3 = new SQLiteParameter("@param3");
            SQLiteParameter param4 = new SQLiteParameter("@param4");


            sql_cmd.CommandText = query1;

            sql_cmd.Parameters.Add(param1);
            sql_cmd.Parameters.Add(param2);
            sql_cmd.Parameters.Add(param3);
            sql_cmd.Parameters.Add(param4);

            param1.Value = person.login;
            param2.Value = person.password;
            param3.Value = person.name;
            param4.Value = person.surname;
            sql_cmd.ExecuteNonQuery();
        }

        //5. Zapisywanie zdjęć do bazy
        public void AddPhotosToDatabase(int id, Image[] photo)
        {
            SQLiteCommand mycommand = new SQLiteCommand(connect);

            SQLiteParameter myparam = new SQLiteParameter("@myparam");
            SQLiteParameter myparam2 = new SQLiteParameter("@myparam2");

            mycommand.CommandText = "INSERT INTO Images(ID_IMAGE,IMAGE) VALUES(@myparam, @myparam2)";

            mycommand.Parameters.Add(myparam);         // <- myparam1 is int
            mycommand.Parameters.Add(myparam2);       // <- in myparam2 I save the blob image
            myparam.Value = id;

            

            foreach (Image item in photo)
            {
                Image x = new Bitmap(item);
                using (MemoryStream ms = new MemoryStream())
                {
                    
                    x.Save(ms, ImageFormat.Png);
                    myparam2.Value = ms.ToArray();
                    mycommand.ExecuteNonQuery();
                }
            }
        }
        //6. Pobranie ID na podstawie jakiejś wartości składowej klasy Person
        public List<int> GetIdFromDatabase(string element)
        {
            SQLiteCommand cmd = new SQLiteCommand(connect);

            SQLiteParameter param = new SQLiteParameter("@param");

            string sql = "SELECT ID FROM Users WHERE LOGIN = @param OR PASSWORD=@param OR NAME=@param OR SURNAME=@param";
            cmd.CommandText = sql;
            cmd.Parameters.Add(param);

            param.Value = element;
            List<int> ids = new List<int>();
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    ids.Add(Convert.ToInt32(reader.GetValue(0).ToString()));
                }
            }
            return ids;
        }

        public string[] GetUsernames()
        {
            List<string> usernames = new List<string>();


            string sql = "SELECT LOGIN FROM Users";
            SQLiteCommand cmd = new SQLiteCommand(sql, connect);
            cmd.CommandType = CommandType.Text;

            AllPhoto photos = new AllPhoto();

            using (SQLiteDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    usernames.Add(dr.GetValue(0).ToString());
                }
            }
            return usernames.ToArray();

        }

        // Klasa do zwracania zwszystkich zdjęć i id {popdupnt 2}
        public class AllPhoto
        {
            public List<int> id;
            public List<Image> photo;

            public AllPhoto()
            {
                id = new List<int>();
                photo = new List<Image>();
            }

        }
    }
}
